﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VLine.InputDto._20220219;
using HCQ2_Model.VLine.OutputDto._20220219;
using HCQ2_Model.VRDD.InputDto._210908;
using HCQ2_Model.VRDD.OutputDto._210908;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDStoreDynamicDAL: IT_VRDDStoreDynamicDAL
    {
        /// <summary>
        ///  获取店铺动态列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<GetStoreNewsOutput> GetStoreNews(GetStoreNewsInput param,out int total)
        {
            sb?.Clear();
            string startDate = string.Empty, 
                endDate = string.Empty;
            if (!string.IsNullOrEmpty(param.dateStart))
                startDate = DateTime.ParseExact(param.dateStart, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if(!string.IsNullOrEmpty(param.dateEnd))
                endDate = DateTime.ParseExact(param.dateEnd, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            sb.Append($@"(SELECT id AS newsID,storeID AS folderID,store_name AS folderName,video_url AS video,ROW_NUMBER() OVER(ORDER BY dy.create_date DESC)AS num,
            picture_url AS image,note,fileID,gpsX,gpsY,locationName,fileName=(SELECT TOP 1 file_name FROM dbo.T_DocumentInfo o WHERE o.file_id=dy.fileID)   FROM dbo.T_VRDDStoreDynamic dy WHERE  ownerID='{param.ownerID}' AND userid='{param.userid}' ");
            if (param.storeID > 0)
                sb.Append($" AND storeID={param.storeID}");
            if (param.type > 0)
                sb.Append($" AND ISNULL(picture_url,'')<>'' ");// 图片
            else
                sb.Append($" AND ISNULL(video_url,'')<>'' ");//视频
            if (!string.IsNullOrEmpty(startDate))
                sb.Append($@" AND create_date>='{startDate}' ");
            if (!string.IsNullOrEmpty(endDate))
                sb.Append($" AND create_date<='{endDate}' ");
            sb.Append(")dy ");
            total = SqlHelper.ExecuteScalarInt32($"SELECT COUNT(0) FROM  {sb}");
            return SqlHelper.ExecuteList<GetStoreNewsOutput>($"SELECT TOP {param.rows} * FROM  {sb} WHERE dy.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取有动态的年-月
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<NewsDateResult> GetNewsDate(NewsDateInput param)
        {
            sb?.Clear();
            sb.Append($@"SELECT CONVERT(INT,LEFT(a.create_date,4)) AS year,a.create_date FROM 
            (SELECT FORMAT(create_date,'yyyy-MM')create_date FROM dbo.T_VRDDStoreDynamic
            WHERE  ownerID='{param.ownerID}' AND userid='{param.userid}' ");
            if (param.storeID > 0)
                sb.Append($" AND storeID={param.storeID} ");
            if (param.type > 0)
                sb.Append($" AND ISNULL(picture_url,'')<>'' ");// 图片
            else
                sb.Append($" AND ISNULL(video_url,'')<>'' ");//视频
            sb.Append(")a GROUP BY a.create_date;");
            return SqlHelper.ExecuteList<NewsDateResult>(sb.ToString());
        }
        /// <summary>
        ///  获取好友店铺动态+自己的动态+推荐
        /// </summary>
        /// <param name="param">参数</param>
        /// <param name="user_id">登录用户id</param>
        /// <param name="owenid">创始人或创建人</param>
        /// <returns></returns>
        public List<FriendStoreNewsOutput> GetFriendStoreNews(FriendStoreNewsInput param, int user_id,out int total,int owenid)
        {
            sb?.Clear();
            //好友动态
            //--usertype:2:合伙人、1：创建人、0：无关
            sb.Append($@" (select *,ROW_NUMBER() OVER(ORDER BY jihe.createDate DESC)AS num,
			isHold=(CASE WHEN usertype=2 THEN 
			(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolder WHERE folder_id=jihe.folderID AND folder_type=1 AND user_guid='{param.userid}') 
			WHEN usertype=1 THEN 
			(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolder WHERE folder_id=jihe.folderID AND folder_type=0 AND user_guid='{param.userid}')
			ELSE CAST(0 AS BIT) END),
			isStar=(CASE WHEN usertype=2 THEN 
			(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolderStar WHERE folder_id=jihe.folderID AND folder_type=1 AND user_guid='{param.userid}') 
			WHEN usertype=1 THEN 
			(SELECT CAST(COUNT(0) AS BIT) FROM T_VRUserFolderStar WHERE folder_id=jihe.folderID AND folder_type=0 AND user_guid='{param.userid}')
			ELSE CAST(0 AS BIT) END),
            --登录用户是否已点赞动态
			isNewsStar=(SELECT CAST(COUNT(0) AS BIT) FROM dbo.T_VRDDUserStoreDynamicStar WHERE userid='{param.userid}' AND newsID=jihe.newsID),
            starNum=(case when usertype =2 then (select star_num from T_VRFounderApply where folder_id=jihe.folder_id and user_id=jihe.founder_id)
            when usertype=1 then (select star_num from T_DocumentFolder where folder_id=jihe.folder_id and founder_id=jihe.founder_id)
            else 0 end),	holdNum=(
            case when usertype =2 then (select focus_num from T_VRFounderApply where folder_id=jihe.folder_id and user_id=jihe.founder_id)
            when usertype=1 then (select focus_num from T_DocumentFolder where folder_id=jihe.folder_id and founder_id=jihe.founder_id)
            else 0 end) from(SELECT *,usertype=(
            case when {owenid}=0  then 1
            when (select count(*)  from T_DocumentFolder where folder_id=f.folder_id and founder_id={owenid})>0 then 1
            else (case when (select count(*) as bit from T_VRFounderApply where folder_id=f.folder_id and user_id={owenid} and apply_status=1)>0 then 2
	            else 0 end )  end ) FROM 
            (SELECT * FROM (SELECT dy.star_num AS newsStarNum,dy.fileID,dy.gpsX,dy.gpsY,dy.locationName,fileName=(SELECT TOP 1 file_name FROM dbo.T_DocumentInfo o 
            WHERE o.file_id=dy.fileID), dy.id AS newsID,dy.storeID AS folderID,ownerID,owner_id,create_date,ownerName=(SELECT user_name
            FROM dbo.T_User WHERE user_id=dy.owner_id),ownerImg=(SELECT user_img
            FROM dbo.T_User WHERE user_id=dy.owner_id),video_url AS video,picture_url AS image,note,
            CONVERT(varchar(100),create_date,120)AS createDate,
            partnerNum =(select count(0) from T_VRFounderApply where folder_id=dy.storeID  and apply_status=1),
			ProductNum =(select count(0) from T_DocumentInfo where file_id in(
			select file_id from  T_DocumentFolderRelation where folder_id=dy.storeID ) and file_status=5 ),         
            store_type=(SELECT stuff((select ',' + store_type from T_VRDDStoreTypeUser WHERE store_id = dy.storeID AND 
            dd_id={user_id} for xml path('')), 1, 1, '') FROM T_VRDDStoreTypeUser a WHERE store_id=dy.storeID AND dd_id={user_id}
            GROUP BY a.store_id) FROM dbo.T_VRDDStoreDynamic dy WHERE owner_id IN(SELECT dd_id_2 FROM dbo.DD_1 WHERE dd_id_1={user_id}) ");
            //path
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($@" AND EXISTS( SELECT folder_id FROM dbo.T_DocumentFolder WHERE 
            folder_path LIKE '%{param.folderPath}%' AND folder_id=dy.storeID) ");
            //type
            if (param.type > 0)
                sb.Append($" AND ISNULL(dy.picture_url,'')<>'' ");// 图片
            else
                sb.Append($" AND ISNULL(dy.video_url,'')<>'' ");//视频
            //自己创建
            sb.Append($@"UNION
			SELECT dy.star_num AS newsStarNum,dy.fileID,dy.gpsX,dy.gpsY,dy.locationName,fileName=(SELECT TOP 1 file_name FROM dbo.T_DocumentInfo o 
            WHERE o.file_id=dy.fileID), dy.id AS newsID,dy.storeID AS folderID,ownerID,owner_id,create_date,ownerName=(SELECT user_name
            FROM dbo.T_User WHERE user_id=dy.owner_id),ownerImg=(SELECT user_img
            FROM dbo.T_User WHERE user_id=dy.owner_id),video_url AS video,picture_url AS image,note,
            CONVERT(varchar(100),create_date,120)AS createDate,
            partnerNum =(select count(0) from T_VRFounderApply where folder_id=dy.storeID  and apply_status=1),
			ProductNum =(select count(0) from T_DocumentInfo where file_id in(
			select file_id from  T_DocumentFolderRelation where folder_id=dy.storeID ) and file_status=5 ),
            store_type=(SELECT stuff((select ',' + store_type from T_VRDDStoreTypeUser WHERE store_id = dy.storeID AND 
            dd_id={user_id} for xml path('')), 1, 1, '') FROM T_VRDDStoreTypeUser a WHERE store_id=dy.storeID AND dd_id={user_id}
            GROUP BY a.store_id) FROM dbo.T_VRDDStoreDynamic dy WHERE owner_id={user_id} ");
            //type
            if (param.type > 0)
                sb.Append($" AND ISNULL(dy.picture_url,'')<>'' ");// 图片
            else
                sb.Append($" AND ISNULL(dy.video_url,'')<>'' ");//视频
            //推荐
            sb.Append($@"UNION
			SELECT dy.star_num AS newsStarNum,dy.fileID,dy.gpsX,dy.gpsY,dy.locationName,fileName=(SELECT TOP 1 file_name FROM dbo.T_DocumentInfo o 
            WHERE o.file_id=dy.fileID), dy.id AS newsID,dy.storeID AS folderID,ownerID,owner_id,create_date,ownerName=(SELECT user_name
            FROM dbo.T_User WHERE user_id=dy.owner_id),ownerImg=(SELECT user_img
            FROM dbo.T_User WHERE user_id=dy.owner_id),video_url AS video,picture_url AS image,note,
            CONVERT(varchar(100),create_date,120)AS createDate, 
            partnerNum =(select count(0) from T_VRFounderApply where folder_id=dy.storeID  and apply_status=1),
			ProductNum =(select count(0) from T_DocumentInfo where file_id in(
			select file_id from  T_DocumentFolderRelation where folder_id=dy.storeID ) and file_status=5 ),
            store_type=(SELECT stuff((select ',' + store_type from T_VRDDStoreTypeUser WHERE store_id = dy.storeID AND 
            dd_id={user_id} for xml path('')), 1, 1, '') FROM T_VRDDStoreTypeUser a WHERE store_id=dy.storeID AND dd_id={user_id}
            GROUP BY a.store_id) FROM dbo.T_VRDDStoreDynamic dy WHERE EXISTS(SELECT id FROM T_VRDDStoreDynamicPushUser WHERE dy_id=dy.id) ");
            //type
            if (param.type > 0)
                sb.Append($" AND ISNULL(dy.picture_url,'')<>'' ");// 图片
            else
                sb.Append($" AND ISNULL(dy.video_url,'')<>'' ");//视频
            sb.Append(")t WHERE 1=1 ");
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($@" AND t.fileName LIKE '%{param.keyword}%' or t.ownerName  LIKE '%{param.keyword}%' or t.store_name LIKE '%{param.keyword}%' ");
            sb.Append($@")d INNER JOIN
            (SELECT folder_id,folder_name AS folderName,folder_url AS folderUrl,folder_path AS folderPath,founder_id,
            folder_note AS folderNote,statr_partner AS partnerEnabled,
            partnerStatus=-1 FROM dbo.T_DocumentFolder)f ON d.folderID=f.folder_id LEFT JOIN 
            (SELECT (CASE WHEN dd_id_1={user_id} THEN dd_id_2 ELSE dd_id_1 END)dd_id FROM dbo.DD_1 WHERE dd_id_1={user_id})t ON d.owner_id=t.dd_id)jihe)dd ");
            total= SqlHelper.ExecuteScalarInt32($"SELECT COUNT(0) FROM  {sb}");
            return SqlHelper.ExecuteList<FriendStoreNewsOutput>($"SELECT TOP {param.rows} * FROM  {sb} WHERE dd.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        ///  获取视频推送人数
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public string GetVideosFeedNumber(string itemCode, int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP 1 m.code_note FROM dbo.T_ItemCodeMenum m WHERE EXISTS(
	            SELECT item_id FROM dbo.T_ItemCode WHERE item_code='{itemCode}' AND item_id=m.item_id
            ) AND m.code_value IN(SELECT CASE WHEN user_type=1 THEN 'vip' ELSE 'user' END FROM dbo.DD_User WHERE du_id={user_id});");
            return Helper.ToString(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取陌生人数量
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="folder_id"></param>
        /// <returns></returns>
        public int GetStrangerNumber(int user_id, int folder_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0) FROM dbo.DD_User dd WHERE dd.du_id<>{user_id} AND NOT EXISTS(
				SELECT dd_id_1 FROM dbo.DD_1 WHERE (dd_id_1={user_id} AND dd_id_2=dd.du_id) OR (dd_id_1=dd.du_id AND dd_id_2={user_id})
            ) AND NOT EXISTS(SELECT id FROM dbo.T_VRFounderApply WHERE folder_id={folder_id} AND (user_id IN(
            SELECT CASE WHEN dd_id_1={user_id} THEN dd_id_2 ELSE dd_id_1 END FROM dbo.DD_1 WHERE dd_id_1={user_id} OR dd_id_2={user_id} 
            ) OR user_id={user_id}) AND apply_status=1);");
            return SqlHelper.ExecuteScalarInt32(sb.ToString());
        }
        /// <summary>
        ///  写入被推荐人
        /// </summary>
        /// <param name="dy_id">动态id</param>
        /// <param name="user_id">推荐人</param>
        /// <param name="folder_id">店铺id</param>
        /// <param name="num">写入数量</param>
        /// <returns></returns>
        public int InsertBeStrangerUser(int dy_id, int user_id, int folder_id,int num)
        {
            sb?.Clear();
            sb.Append($@"INSERT INTO T_VRDDStoreDynamicPushUser(dy_id,user_id) SELECT TOP {num} {dy_id},dd.du_id FROM dbo.DD_User dd
			WHERE dd.du_id<>{user_id} AND NOT EXISTS(SELECT dd_id_1 FROM dbo.DD_1 WHERE (dd_id_1={user_id} AND dd_id_2=dd.du_id) 
			OR (dd_id_1=dd.du_id AND dd_id_2={user_id})) AND NOT EXISTS(SELECT id FROM dbo.T_VRFounderApply WHERE 
			folder_id={folder_id} AND (user_id IN(SELECT CASE WHEN dd_id_1={user_id} THEN dd_id_2 ELSE dd_id_1 END 
			FROM dbo.DD_1 WHERE dd_id_1={user_id} OR dd_id_2={user_id}) OR user_id={user_id}) AND apply_status=1);");
            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }
    }
}